-- **************************************************************************
--    Project Name:   操作管理-错发率明细
--    Job Name:       jms_dm.dm_transfer_mistake_send_detail_dt
--    Author :        季修魁
--    date：          2023/08/30
--    modify：        郭瑞玲
--    date：          2023/12/21
-- **************************************************************************
-- **************************************************************************

----路由逻辑更新换成静态路由  modify jixk 2023-11-24
insert overwrite table jms_tmp.tmp_tab_route_whole_center_collect partition (dt)
SELECT
    start_code
     ,end_code
     ,if(trim(arr_next_code_desc)='',null,arr_next_code_desc)      as arr_next_code_desc
     ,if(trim(route_code_desc)=''   ,null,route_code_desc   )      as route_code_desc
     ,if(trim(route_name_desc)=''   ,null,route_name_desc   )      as route_name_desc
     ,if(trim(main_route_code)=''   ,null,main_route_code   )      as main_route_code
     ,if(trim(main_route_name)=''   ,null,main_route_name   )      as main_route_name
     ,if(trim(standby_route_code)='',null,standby_route_code)      as standby_route_code
     ,if(trim(standby_route_name)='',null,standby_route_name)      as standby_route_name
     ,null  as substr_route_type
     ,null  as end_collect_code
     ,null  as end_collect_name
     ,dt
FROM
    (
        select start_code
             ,end_code
             ,regexp_replace(concat_ws(',', sort_array(collect_list(next_code_desc))),'(8主:|9备:)','')      as arr_next_code_desc
             --各个路由的所有中间节点用，分割
             ,regexp_replace(concat_ws(',', sort_array(collect_list(route_code))),'-',',')            as route_code_desc
             ,regexp_replace(concat_ws(',', sort_array(collect_list(route_name))),'-',',')            as route_name_desc
             --各个路由用，分割
             ,concat_ws(',', sort_array(collect_list(main_route_code)))     as main_route_code
             ,concat_ws(',', sort_array(collect_list(main_route_name)))     as main_route_name
             ,concat_ws(',', sort_array(collect_list(standby_route_code)))  as standby_route_code
             ,concat_ws(',', sort_array(collect_list(standby_route_name)))  as standby_route_name
             ,dt
        from (
                 select  start_code
                      ,if(route_name is not null,route_code,null) as route_code
                      ,route_name
                      ,if(is_main_route=1 and trim(route_code)!='' and route_name is not null,route_code,null)         as main_route_code
                      ,if(is_main_route=1,route_name,null)         as main_route_name
                      ,if(is_main_route<>1,route_code,null)        as standby_route_code
                      ,if(is_main_route<>1,route_name,null)        as standby_route_name
                      ,end_code
                      ,concat(case when is_main_route=1 then '8主'  else '9备' end,':',e1_end_code) as next_code_desc
                      ,dt
                 from (
                          SELECT in_to_code        as start_code        ---始发转运中心编码
                               ,out_from_code      as end_code          ---目的转运中心编码
                               ,is_main_route
                               ,e1_end_code
                               ,e2_end_code
                               ,e3_end_code
                               ,e4_end_code
                               ,e5_end_code
                               ,e6_end_code
                               ,regexp_replace(regexp_replace(concat(nvl(in_to_code,'s'),',',nvl(e1_end_code,'s'),',',nvl(e2_end_code,'s'),',',nvl(e3_end_code,'s'),',',nvl(e4_end_code,'s'),',',nvl(e5_end_code,'s'),',',nvl(e6_end_code,'s')),',s',''),'s','')  as route_code
                               --中心到集散这块 whole_route这个可能没值 845101-0454003
                               ,if(trim(whole_route)='',null,whole_route)  as route_name
                               ,date_add(dt,1) as dt
                          from jms_dm.dm_route_whole_substr_base_dt
                          where dt>=date_add('{{ execution_date | cst_ds }}',-2) and dt<=date_add('{{ execution_date | cst_ds }}',-1)
                            --dt between '2024-01-01' and '2024-01-28'
                            and substr_route_type ='中心-中心'
                          group by in_to_code
                                 ,out_from_code
                                 ,is_main_route
                                 ,e1_end_code
                                 ,e2_end_code
                                 ,e3_end_code
                                 ,e4_end_code
                                 ,e5_end_code
                                 ,e6_end_code
                                 ,regexp_replace(regexp_replace(concat(nvl(in_to_code,'s'),',',nvl(e1_end_code,'s'),',',nvl(e2_end_code,'s'),',',nvl(e3_end_code,'s'),',',nvl(e4_end_code,'s'),',',nvl(e5_end_code,'s'),',',nvl(e6_end_code,'s')),',s',''),'s','')
                                 ,whole_route
                                 ,date_add(dt,1)
                          union all
                          SELECT in_to_code           as start_code        ---始发转运中心编码
                               ,out_collect_code      as end_code          ---目的转运中心编码
                               ,is_main_route
                               ,e1_end_code
                               ,e2_end_code
                               ,e3_end_code
                               ,e4_end_code
                               ,e5_end_code
                               ,e6_end_code
                               ,regexp_replace(regexp_replace(concat(nvl(in_to_code,'s'),',',nvl(e1_end_code,'s'),',',nvl(e2_end_code,'s'),',',nvl(e3_end_code,'s'),',',nvl(e4_end_code,'s'),',',nvl(e5_end_code,'s'),',',nvl(e6_end_code,'s')),',s',''),'s','') as route_code
                               ,if(trim(whole_route)='',null,whole_route)  as route_name
                               ,date_add(dt,1) as dt
                          from jms_dm.dm_route_whole_substr_base_dt
                          where dt>=date_add('{{ execution_date | cst_ds }}',-2) and dt<=date_add('{{ execution_date | cst_ds }}',-1)
                                 -- dt between '2024-01-01' and '2024-01-28'
                            and substr_route_type ='中心-集散'
                          group by in_to_code
                                 ,out_collect_code
                                 ,is_main_route
                                 ,e1_end_code
                                 ,e2_end_code
                                 ,e3_end_code
                                 ,e4_end_code
                                 ,e5_end_code
                                 ,e6_end_code
                                 ,regexp_replace(regexp_replace(concat(nvl(in_to_code,'s'),',',nvl(e1_end_code,'s'),',',nvl(e2_end_code,'s'),',',nvl(e3_end_code,'s'),',',nvl(e4_end_code,'s'),',',nvl(e5_end_code,'s'),',',nvl(e6_end_code,'s')),',s',''),'s','')
                                 ,whole_route
                                 ,date_add(dt,1)
                      ) a
             )t
        group by start_code,end_code,dt
    )tt
    distribute by dt;
------------------------------------------------------------------------------------------------------------------------
with sign_waybill_1 as ( --最早签收+签收网点的可派货中心
    select
        waybill_no
         ,signed_time
         ,signed_site
         ,signed_site_code
    from (
             select waybill_no
                  ,signed_time
                  ,signed_site
                  ,signed_site_code
                  ,row_number() over(partition by waybill_no order by signed_time) as rk
             from
                 (
                     SELECT
                         waybill_no
                          ,signed_time
                          ,signed_site
                          ,signed_site_code
                     FROM jms_dwd.dwd_tab_barscan_sign_base_dt
                     where dt between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
                       and to_date(signed_time)='{{ execution_date | cst_ds }}'

                     union all

                     select
                         waybill_no
                          ,scan_time      as signed_time
                          ,scan_site      as signed_site
                          ,scan_site_code as signed_site_code
                     from jms_dwd.dwd_tab_barscan_collect_base_dt
                     where dt between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
                       and to_date(scan_time)='{{ execution_date | cst_ds }}'
                       and scan_type='出库扫描'
                 ) signed_tab
         ) t1
    where rk=1 and signed_site_code is not null and substr(waybill_no,1,2)='JT'
),

----t-1实际签收的数据
     sign_waybill as (  --最早签收+签收网点的可派货中心
         select t1.waybill_no
              ,t1.signed_time
              ,t1.signed_site
              ,t1.signed_site_code
              ,t2.out_distribution_code_new_all  as out_distribution_code  --签收网点的可派货中心
              ,t2.out_distribution_name_new_all  as out_distribution_name
         from sign_waybill_1 t1
                  left join (select a.*
                                  ,b.out_distribution_code_new as entrepot_out_distribution_code_new
                                  ,b.out_distribution_name_new as entrepot_out_distribution_name_new
                                  ,case when nvl(a.out_distribution_code_new,'')<>'' and nvl(b.out_distribution_code_new,'')<>''
                                            then concat(a.out_distribution_code_new,',',b.out_distribution_code_new)
                                        when nvl(a.out_distribution_code_new,'')<>'' and nvl(b.out_distribution_code_new,'')=''
                                            then a.out_distribution_code_new
                                        when nvl(a.out_distribution_code_new,'')='' and nvl(b.out_distribution_code_new,'')<>''
                                            then b.out_distribution_code_new
                 end as out_distribution_code_new_all
                                  ,case when nvl(a.out_distribution_code_new,'')<>'' and nvl(b.out_distribution_code_new,'')<>''
                                            then concat(a.out_distribution_name_new,',',b.out_distribution_name_new)
                                        when nvl(a.out_distribution_code_new,'')<>'' and nvl(b.out_distribution_code_new,'')=''
                                            then a.out_distribution_name_new
                                        when nvl(a.out_distribution_code_new,'')='' and nvl(b.out_distribution_code_new,'')<>''
                                            then b.out_distribution_name_new
                 end as out_distribution_name_new_all
                             from (
                                      select code
                                           ,name
                                           ,out_distribution_code_new ---可派货中心
                                           ,out_distribution_name_new
                                           ,out_entrepot_code_new     --可派货集散
                                           ,out_entrepot_name_new
                                      from jms_dim.dim_network_whole_massage
                                  ) a
                                      --取可派货集散的可派货中心
                                      left join (select * from jms_dim.dim_network_whole_massage where network_type= 5) b    ---集散点
                                                on a.out_entrepot_code_new=b.code
         ) t2
                            on t1.signed_site_code =t2.code
     ),

--中心发件下一站为中心barscan_centersend

     center_send as (
         select
             t1.waybill_no
              ,t1.scan_time
              ,t1.scan_site
              ,t1.scan_site_code
              ,t1.scan_user
              ,t1.scan_user_code
              ,t1.source
              ,t1.pistol_id
              ,t1.next_station_code
              ,t1.next_network_type
              ,t1.package_code
              ,t1.transfer_code
              ,t1.transport_type
         from (
                  select
                      centersend.waybill_no
                       ,centersend.scan_time
                       ,centersend.scan_site
                       ,centersend.scan_site_code
                       ,centersend.scan_user
                       ,centersend.scan_user_code
                       ,centersend.source
                       ,centersend.pistol_id
                       ,centersend.next_station_code
                       ,centersend.next_network_type
                       ,centersend.package_code
                       ,centersend.transfer_code
                       ,centersend.transport_type
                       ,row_number() over(partition by centersend.waybill_no,centersend.scan_site_code order by centersend.scan_time ) as rk1
                  from jms_dwd.dwd_tab_barscan_centersend_base_dt  centersend                ---中心发件扫描表
                  where centersend.dt between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
                    and centersend.next_network_type = 2              ---转运中心
              ) t1 where rk1=1  and  nvl(transport_type,'') <> '航空运输'   --剔除航空转运
     ),

-----中心到件扫描centerarrival
     center_send_arrival_info as (
         select *
         from (
                  select waybill_no
                       ,send_station_network_code                                                   as scan_site_code
                       ,to_date(send_station_loading_later_time)                                    as scan_time
                       ,next_station_network_code                                                   as arrival_site_code         --实际到件下一站
                       ,row_number() over(partition by waybill_no,send_station_network_code
                           order by send_station_loading_later_time ) as rk1
                  from jms_dwd.dwd_wide_sign_trace_waybill_step_dt
                  where dt='{{ execution_date | cst_ds }}'
                    and send_station_network_type='2'                                                           ---中心
                    and nvl(send_station_send_next_network_type,send_station_loading_next_network_type)='2'     ---下一站为中心
              ) t
         where rk1=1
     ),
---运单信息  -30天
     tab_waybill as (
         select way.waybill_no
              ,way.first_code
              ,way.second_code
              ,way.third_code
              ,if(way.ordersource_name in ('桃花岛跨境','桃花岛跨境（逆）','桃花岛','紫金山','逍遥峰','极地湾','麦田圈','苏宁','有赞','七星潭'),way.ordersource_name,'其他') ordersource_name
              ,fst.dest_center_code
              ,fst.dest_center_name
              ,way.terminal_dispatch_code
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt way
                  left join jms_dim.dim_first_second_third_code_info fst
                            on fst.first_code= way.first_code
                                and fst.code_type=1
         where way.dt between date_add('{{ execution_date | cst_ds }}',-31)  and '{{ execution_date | cst_ds }}'
     ),

---订单信息 jms_dwd.dwd_yl_oms_oms_order_incre_dt
     tab_orderbill as (
         select
             t1.waybill_id as waybill_no
              ,t1.terminal_dispatch_code                  as terminal_dispatch_code
              ,t2.dest_center_code
              ,t2.dest_center_name
         from jms_dwd.dwd_yl_oms_oms_order_dt          t1
                  left join jms_dim.dim_first_second_third_code_info t2 on split(terminal_dispatch_code,",")[0] = t2.first_code and t2.code_type=1
         where dt between date_add('{{ execution_date | cst_ds }}',-31)  and '{{ execution_date | cst_ds }}'

     ),

--建包
     tab_bagging as (
         select *
         from (
                  select waybill_no
                       ,scan_site_code
                       ,scan_site
                       ,package_code
                       ,source
                       ,row_number() over(partition by waybill_no,scan_site_code,package_code order by scan_time) as rk
                  from jms_dwd.dwd_tab_barscan_bagging_base_dt
                  where dt between date_add('{{ execution_date | cst_ds }}',-35) and '{{ execution_date | cst_ds }}'
                    and substr(waybill_no,1,1)!='B'
              ) t
         where rk=1
     ),

---剔除件
     tab_delete as (
         select
             billcode
              ,scan_type
              ,to_date(scan_time) as scan_time
              ,scan_site_code
         from jms_dwd.dwd_tab_barscan_scan_delete_base_dt
         where dt between date_add('{{ execution_date | cst_ds }}',-60) and '{{ execution_date | cst_ds }}'
           and scan_type='发件扫描'
         group by
             billcode
                ,scan_type
                ,to_date(scan_time)
                ,scan_site_code
     ),

---调度指令  add jixk 2023-11-06
     tab_dispatch as ( --调度指令复核通过的任务号
         select b.task_no
         from jms_dim.dim_sys_notice_base a --调度指令表
                  left join
              (    select notice_id
                        , task_no  --任务号
                        , row_number() over (partition by notice_id,task_no order by dt) rn
                   from jms_dim.dim_adjust_detail_dt
                   where dt >= date_add('{{ execution_date | cst_ds }}', -31) and dt <= '{{ execution_date | cst_ds }}'
                     and is_delete = 1
              ) b on a.id = b.notice_id and b.rn = 1
         where date(a.create_time) >= date_add('{{ execution_date | cst_ds }}', -31)
    and a.review_status = 1 --1 复核通过
group by b.task_no
    ),

---差异化建包-末端末端机构
    diff_package as (
select express_center_short_code,express_center_code,express_center_name
        ,case when network_type='6' then express_center_code else express_org_code end  as express_org_code   ---末端机构code
        ,case when network_type='6' then express_center_name else express_org_name end  as express_org_name   ---末端机构名称
        ,dt
from (
    select a.express_center_short_code        --一段码
        ,a.express_center_code             ---目的中心code
        ,c.name as express_center_name       ---目的中心名称
        ,a.express_org_code                ---末端机构code
        ,a.express_org_name                ---末端机构名称
        ,a.dt                              ---
        ,b.network_type                    ---末端机构类型
        ,row_number() over(partition by express_center_short_code,dt order by express_center_code desc ) as rn1
    from (select * from jms_dim.dim_yl_sys_package_different_base_dt
    where dt>= date_add('{{ execution_date | cst_ds }}', -45) and dt<='{{ execution_date | cst_ds }}'
    ) a
    left join jms_dim.dim_network_whole_massage       b
    on a.express_org_code = b.code
    left join jms_dim.dim_network_whole_massage       c
    on a.express_center_code = c.code
    ) t where rn1=1
    ),

---------整合数据
    tab_way_res1 as (
select  a.waybill_no               as waybill_no
        ,a.signed_time              as sign_time
        ,a.signed_site              as sign_site
        ,a.signed_site_code         as sign_site_code
        ,a.out_distribution_code    as out_distribution_code
        ,a.out_distribution_name    as out_distribution_name
        ,b.scan_time                as send_time
        ,''                         as center_name
        ,b.scan_site_code           as center_code                 ---发件中心
        ,b.scan_user                as scan_user
        ,b.scan_user_code           as scan_user_code
        ,b.source                   as source
        ,b.pistol_id                as pistol_id
        ,b.next_station_code        as next_station_code           ---下一站中心
        ,''                         as next_network_type
        ,b.package_code             as package_code
        ,b.transfer_code            as transfer_code
        ,c.ordersource_name
        ,c.dest_center_code         as waybill_dest_center_code
        ,c.dest_center_name         as waybill_dest_center_name
        ,c.terminal_dispatch_code   as waybill_terminal_dispatch_code
        ,d.dest_center_code         as order_dest_center_code
        ,d.dest_center_name         as order_dest_center_name
        ,d.terminal_dispatch_code   as order_terminal_dispatch_code
        ,e.arrival_site_code        as arrival_site_code
        ,''                         as arrival_site_name
        ,''                         as arrival_scan_time
from sign_waybill       a
    inner join center_send   b on a.waybill_no = b.waybill_no
    left join tab_waybill   c on a.waybill_no = c.waybill_no
    left join tab_orderbill d on a.waybill_no = d.waybill_no
    left join center_send_arrival_info e on b.waybill_no=e.waybill_no and b.scan_site_code=e.scan_site_code
    ),

    tab_way_res2 as (--关联退件取到中心发件当时的三段码值
select tt.*
        ,case when is_reback_before=1 then old_reback_terminal_code
    when is_reback_after=1  then new_reback_terminal_code
    else coalesce(order_terminal_dispatch_code,waybill_terminal_dispatch_code)
    end                                                                                                as terminal_dispatch_code   ---三段码
        ,case when is_reback_before=1 then split(old_reback_terminal_code,',')[0]
    when is_reback_after=1  then split(new_reback_terminal_code,' ')[0]
    else split(coalesce(order_terminal_dispatch_code,waybill_terminal_dispatch_code),',')[0]
    end                                                                                                as first_code
        ,case when is_reback_before=1 then split(old_reback_terminal_code,',')[1]
    when is_reback_after=1  then split(new_reback_terminal_code,' ')[1]
    else split(coalesce(order_terminal_dispatch_code,waybill_terminal_dispatch_code),',')[1]
    end                                                                                                as second_code
        ,case when is_reback_before=1 then split(old_reback_terminal_code,',')[2]
    when is_reback_after=1  then split(new_reback_terminal_code,' ')[2]
    else split(coalesce(order_terminal_dispatch_code,waybill_terminal_dispatch_code),',')[2]
    end                                                                                                as third_code
   ,date(sign_time) as dt
from
    (
    select
    t.*
        ,reback2.receiver_province_id                                             as old_receiver_province_id
        ,reback2.receiver_city_id                                                 as old_receiver_city_id
        ,reback2.receiver_area_id                                                 as old_receiver_area_id
        ,if(reback2.waybill_no is not null,1,0 )                                  as is_reback_before            --是否退转
        ,coalesce(reback2.apply_type_code,t.new_apply_type_code)                  as apply_type_code             --退转类型
        ,reback2.terminal_dispatch_code                                           as old_reback_terminal_code
        ,row_number() over(partition by t.waybill_no,t.center_code,t.next_station_code,t.sign_time
    order by unix_timestamp(t.send_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(reback2.print_time,'yyyy-MM-dd HH:mm:ss'))  as rk2
    from (
    select
    res1.*
        ,reback1.new_terminal_dispatch_code      as new_reback_terminal_code
        ,reback1.new_receiver_province_id        as new_receiver_province_id
        ,reback1.new_receiver_city_id            as new_receiver_city_id
        ,reback1.new_receiver_area_id            as new_receiver_area_id
        ,reback1.apply_type_code                 as new_apply_type_code
        ,if(reback1.waybill_no is not null,1,0 ) as is_reback_after
        ,row_number() over(partition by res1.waybill_no,res1.center_code,res1.next_station_code
    order by unix_timestamp(res1.send_time,'yyyy-MM-dd HH:mm:ss')-unix_timestamp(reback1.print_time,'yyyy-MM-dd HH:mm:ss'))  as rk1
    from tab_way_res1 res1
    left join jms_dwd.dwd_tab_reback_transfer_express_base reback1
    on res1.waybill_no=reback1.waybill_no and reback1.print_time<res1.send_time
    and date(reback1.print_time) between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
    ) t
    left join jms_dwd.dwd_tab_reback_transfer_express_base reback2
    on reback2.waybill_no=t.waybill_no and reback2.print_time>t.send_time
    and date(reback2.print_time) between date_add('{{ execution_date | cst_ds }}',-30) and '{{ execution_date | cst_ds }}'
    where t.rk1=1
    ) tt where tt.rk2=1
    )
    insert overwrite table jms_tmp.tmp_transfer_mistake_send_detail_mid_dt partition(dt)
    select * from tab_way_res2  distribute by dt,abs(hash(waybill_no))%100;

-----------------------------------------------------------------------------------------
with t_first as (
    --一段码
    select
        code as first_code,center_code,center_name,dest_center_code,dest_center_name,is_enable
         ,case when is_enable=1 then min(update_time)      when is_enable=2 then '1970-12-31 00:00:00'  end as  start_time
         ,case when is_enable=1 then '9999-12-31 00:00:00' when is_enable=2 then max(update_time)       end as  end_time
    from
        (  --刷历史数据要带上dt
            select code,center_code,center_name,update_time, dest_center_code,dest_center_name,is_enable
            from jms_dim.dim_lmdm_sys_first_code
            where is_delete=1 and is_enable=1
              --and dt='{{ execution_date | cst_ds }}'
            group by  code,center_code,center_name,update_time, dest_center_code,dest_center_name,is_enable
            union all
            select code,center_code,center_name,update_time, dest_center_code,dest_center_name,is_enable
            from jms_dim.dim_lmdm_sys_first_code
            where is_delete=1 and is_enable=2
             -- and dt='{{ execution_date | cst_ds }}'
            group by  code,center_code,center_name,update_time, dest_center_code,dest_center_name,is_enable
        )fst
    group by code,center_code,center_name, dest_center_code,dest_center_name,is_enable
),
     diff_package as (
         select
             express_center_short_code
              ,express_center_code
              ,express_center_name
              ,express_center_code as express_org_code
              ,express_center_name as express_org_name
              ,min(update_time) as update_time
         from

             (
                 select a.express_center_short_code        --一段码
                      ,a.express_center_code             ---目的中心code
                      ,c.name as express_center_name       ---目的中心名称
                      ,case when b.network_type='6' then express_center_code else a.express_org_code end as express_org_code   ---末端机构code
                      ,case when b.network_type='6' then c.name  else a.express_org_name end as express_org_name   ---末端机构名称
                      ,b.network_type                    ---末端机构类型
                      ,a.update_time
                 from  jms_dim.dim_yl_sys_package_different_base a
                           left join jms_dim.dim_network_whole_massage  b  on a.express_org_code = b.code
                           left join jms_dim.dim_network_whole_massage  c  on a.express_center_code = c.code
             )t
         group by express_center_short_code,express_center_code,express_center_name

     ),
     ---调度指令  add jixk 2023-11-06
     tab_dispatch as ( --调度指令复核通过的任务号
         select b.task_no
         from jms_dim.dim_sys_notice_base a --调度指令表
                  left join
              (    select notice_id
                        , task_no  --任务号
                        , row_number() over (partition by notice_id,task_no order by dt) rn
                   from jms_dim.dim_adjust_detail_dt
                   where dt >= date_add('{{ execution_date | cst_ds }}', -31) and dt <= '{{ execution_date | cst_ds }}'
                     and is_delete = 1
              ) b on a.id = b.notice_id and b.rn = 1
         where date(a.create_time) >= date_add('{{ execution_date | cst_ds }}', -31)
    and a.review_status = 1 --1 复核通过
group by b.task_no
    ),
---剔除件
    tab_delete as (
select
    billcode
        ,scan_type
        ,to_date(scan_time) as scan_time
        ,scan_site_code
from jms_dwd.dwd_tab_barscan_scan_delete_base_dt
where dt between date_add('{{ execution_date | cst_ds }}',-60) and '{{ execution_date | cst_ds }}'
  and scan_type='发件扫描'
group by
    billcode
        ,scan_type
        ,to_date(scan_time)
        ,scan_site_code
    ),
--建包
    tab_bagging as (
select *
from (
    select waybill_no
        ,scan_site_code
        ,scan_site
        ,package_code
        ,source
        ,row_number() over(partition by waybill_no,scan_site_code,package_code order by scan_time) as rk
    from jms_dwd.dwd_tab_barscan_bagging_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-35) and '{{ execution_date | cst_ds }}'
    and substr(waybill_no,1,1)!='B'
    ) t
where rk=1
    ),
    tab_way_res3 as ( --取末端中心
select
    t.*
        , coalesce (fst.dest_center_code, diff_package.express_center_code) as fst_dest_center_code_new                               --一段码对应目的中心
        , coalesce (fst.dest_center_code, diff_package.express_center_code, waybill_wide.end_center_network_code) as dest_center_code --目的中心
        , coalesce (fst.center_code, diff_package.express_org_code) as first_end_org_code                                             --一段码对应的末端机构code
        , coalesce (fst.center_name, diff_package.express_org_name) as first_end_org_name                                             --一段码对应的末端机构name
from jms_tmp.tmp_transfer_mistake_send_detail_mid_dt  t
    /*left join jms_dim.dim_first_second_third_code_info_dt fst
    on fst.dt >=date_add('{{ execution_date | cst_ds }}',-30) and fst.dt<='{{ execution_date | cst_ds }}'
    and t.first_code = fst.first_code
    and to_date(t.send_time) = fst.dt
    and fst.code_type=1*/
    left join t_first fst on  t.send_time between fst.start_time and fst.end_time  and t.first_code = fst.first_code
    /*left join diff_package diff_package
    on t.first_code = diff_package.express_center_short_code
    and to_date(t.send_time) = diff_package.dt ---add jixk 2023-11-10 */
    left join diff_package diff_package
    on t.first_code = diff_package.express_center_short_code
    and t.send_time >= diff_package.update_time
    left join (select waybill_no,end_center_network_code from jms_dwd.dwd_wide_sign_summary_waybill_dt where dt='{{ execution_date | cst_ds }}') waybill_wide
    on t.waybill_no = waybill_wide.waybill_no
where t.dt='{{ execution_date | cst_ds }}'
    )

    ,transfer_mistake_merge_test1 as (
---insert overwrite table bidefault.dm_transfer_mistake_merge_test1
select
    a.waybill_no
        ,a.sign_time
        ,a.sign_site
        ,a.sign_site_code
        ,a.out_distribution_code
        ,a.out_distribution_name
        ,a.send_time
        ,net2.name      as  center_name
        ,a.center_code
        ,a.scan_user
        ,a.scan_user_code
        ,a.source
        ,a.pistol_id
        ,a.next_station_code
        ,a.next_network_type
        ,a.package_code
        ,a.transfer_code
        ,a.ordersource_name
        ,a.waybill_dest_center_code
        ,a.waybill_dest_center_name
        ,a.waybill_terminal_dispatch_code
        ,a.order_dest_center_code
        ,a.order_dest_center_name
        ,a.order_terminal_dispatch_code
        ,a.new_reback_terminal_code
        ,a.new_receiver_province_id
        ,a.new_receiver_city_id
        ,a.new_receiver_area_id
        ,a.is_reback_before
        ,a.rk1
        ,a.old_receiver_province_id
        ,a.old_receiver_city_id
        ,a.old_receiver_area_id
        ,a.is_reback_after
        ,a.apply_type_code
        ,a.old_reback_terminal_code
        ,a.rk2
        ,a.terminal_dispatch_code
        ,a.first_code
        ,a.second_code
        ,a.third_code
        ,a.fst_dest_center_code_new                                       as fst_dest_center_code
        ,net.name                                                         as fst_dest_center_name
        ,dest_center_code
        ,null as arr_next_name_desc
        ,null as arr_next_code_desc
        ,tab_dispatch.task_no                        as dispatch_task_no
        ,tab_temp_tran.actual_shipment_no            as temp_actual_shipment_no
        ,electr.package_code                         as package_number
        ,coalesce(tab_bagging.scan_site_code,electr.create_network_code)                        as create_package_org_code
        ,coalesce(tab_bagging.scan_site,electr.create_network_name)                             as create_package_org_name
        ,if(tab_delete.billcode is not null,1,0)     as is_delete_flag
        ,if(tab_bagging.waybill_no is not null,1,0)  as is_tab_bagging_flag
        ,tab_bagging.source                          as tab_bagging_source
        ,route1.main_route_code        as  main_route_code
        ,route1.main_route_name        as  main_route_name
        ,route1.standby_route_code     as  standby_route_code
        ,route1.standby_route_name     as  standby_route_name
        ,route1.route_code_desc        as  route_code_desc
        ,route1.route_name_desc        as  route_name_desc
        ,a.arrival_site_code
        ,net1.name  as arrival_site_name
        ,a.arrival_scan_time
        ,a.first_end_org_code
        ,a.first_end_org_name
    --t-1 
        ,route0.arr_next_code_desc     as arr_next_name_desc0
        ,route0.arr_next_code_desc     as arr_next_code_desc0
        ,route0.main_route_code        as main_route_code0
        ,route0.main_route_name        as main_route_name0
        ,route0.standby_route_code     as standby_route_code0
        ,route0.standby_route_name     as standby_route_name0
        ,route0.route_code_desc        as route_code_desc0
        ,route0.route_name_desc        as route_name_desc0
    --t+1
        ,route2.arr_next_code_desc     as arr_next_name_desc2
        ,route2.arr_next_code_desc     as arr_next_code_desc2
        ,route2.main_route_code        as main_route_code2
        ,route2.main_route_name        as main_route_name2
        ,route2.standby_route_code     as standby_route_code2
        ,route2.standby_route_name     as standby_route_name2
        ,route2.route_code_desc        as route_code_desc2
        ,route2.route_name_desc        as route_name_desc2
from tab_way_res3  a
--T-1 版路由
    left join jms_tmp.tmp_tab_route_whole_center_collect route0
on route0.dt between date_add('{{ execution_date | cst_ds }}',-31) and '{{ execution_date | cst_ds }}'     ----中心-中心
    and a.center_code = route0.start_code
    and a.first_end_org_code=route0.end_code
    and to_date(a.send_time) = date_add(route0.dt,1)
--T 版路由
    left join jms_tmp.tmp_tab_route_whole_center_collect route1
    on route1.dt between date_add('{{ execution_date | cst_ds }}',-31) and '{{ execution_date | cst_ds }}'      ----中心-中心
    and a.center_code = route1.start_code
    and a.first_end_org_code=route1.end_code
    and to_date(a.send_time) = route1.dt
--T+1 版路由
    left join jms_tmp.tmp_tab_route_whole_center_collect route2
    on route2.dt between date_add('{{ execution_date | cst_ds }}',-31) and '{{ execution_date | cst_ds }}'        ----中心-中心
    and a.center_code = route2.start_code
    and a.first_end_org_code=route2.end_code
    and to_date(a.send_time) = date_add(route2.dt,-1)
    left join tab_dispatch  on a.transfer_code = tab_dispatch.task_no
    left join ( --临时转运表
    select actual_shipment_no,demand_transfer_center_code,assist_transfer_center_code
    from jms_dwd.dwd_tms_temp_transport_base_dt           --临时转运表(取已确认待确定已申诉状态)
    where dt >= date_add(date('{{ execution_date | cst_ds }}'),-31) and dt<='{{ execution_date | cst_ds }}'
    and nvl(data_status,-1) <> 0--数据状态 0：已废弃 1：待确认 2：已确认 3：已申诉
    group by actual_shipment_no,demand_transfer_center_code,assist_transfer_center_code
    ) tab_temp_tran
    on a.transfer_code = tab_temp_tran.actual_shipment_no
    and a.center_code   = tab_temp_tran.demand_transfer_center_code          ---发件中心=需求中心
    and a.next_station_code  = tab_temp_tran.assist_transfer_center_code     ---发件下一站中心=协助中心
    left join (  ---电子包牌
    select package_code,package_number,create_network_name,create_network_code --一段码,包号，创建网点
    from jms_dwd.dwd_tab_electronic_package_list_base_dt
    where dt between date_add('{{ execution_date | cst_ds }}',-60)  and '{{ execution_date | cst_ds }}'
    group by package_code,package_number,create_network_name,create_network_code
    ) electr on a.package_code=electr.package_number
    left join tab_delete on tab_delete.billcode=a.waybill_no and to_date(tab_delete.scan_time)=to_date(a.send_time) and tab_delete.scan_site_code=a.center_code
    left join tab_bagging on tab_bagging.waybill_no =a.waybill_no and tab_bagging.package_code=a.package_code and  tab_bagging.scan_site_code=a.center_code
    left join jms_dim.dim_network_whole_massage net  on net.code=a.fst_dest_center_code_new
    left join jms_dim.dim_network_whole_massage net1 on net1.code=a.arrival_site_code
    left join jms_dim.dim_network_whole_massage net2 on net2.code=a.center_code
    )
-----------------------------------------------------------------------
insert overwrite table jms_dm.dm_transfer_mistake_send_detail_dt partition (dt)
select
    date_time
     ,bill_code
     ,ordersource_name
     ,package_code
     ,package_number
     ,scan_user
     ,scan_user_code
     ,scan_pistol_id
     ,scan_data_source
     ,send_time
     ,arr_next_name_desc
     ,arr_next_code_desc
     ,next_station
     ,next_station_code
     ,agent_code
     ,agent_name
     ,center_name
     ,center_code
     ,dest_center_name
     ,dest_center_code
     ,sign_time
     ,sign_site
     ,sign_site_code
     ,sign_cande_codes
     ,sign_cande_names
     ,first_code
     ,second_code
     ,third_code
     ,fst_dest_center_name
     ,fst_dest_center_code
     ,apply_type_code
     ,case when mistake_type = 0 then 0    --无错分优先
           when mistake_type0 = 0 then 0
           when mistake_type2 = 0 then 0
           else 1 end is_mistake
     ,case when mistake_type  = 0 then 0  --错分类型
           when mistake_type0 = 0 then 0
           when mistake_type2 = 0 then 0
    --
           when mistake_type  = 1 then 1  --无一段码
           when mistake_type0 = 1 then 1
           when mistake_type2 = 1 then 1
    --
           when mistake_type  = 13 then 13  --一段码无目的中心
           when mistake_type0 = 13 then 13
           when mistake_type2 = 13 then 13
    --
           when mistake_type  = 2 then 2   --无路由
           when mistake_type0 = 2 then 2
           when mistake_type2 = 2 then 2
    --
           when mistake_type  = 3 then 3  --调度指令
           when mistake_type0 = 3 then 3
           when mistake_type2 = 3 then 3
    --
           when mistake_type  = 4 then 4  --临时转运
           when mistake_type0 = 4 then 4
           when mistake_type2 = 4 then 4
    --
           when mistake_type  = 8 then 8  --退回件
           when mistake_type0 = 8 then 8
           when mistake_type2 = 8 then 8
    ---
           when mistake_type  = 9 then 9  --转寄件
           when mistake_type0 = 9 then 9
           when mistake_type2 = 9 then 9
    --
           when mistake_type  = 3 then 3  --调度指令
           when mistake_type0 = 3 then 3
           when mistake_type2 = 3 then 3
    --
           when mistake_type  = 5 then 5  --一段码异常
           when mistake_type0 = 5 then 5
           when mistake_type2 = 5 then 5
    --
           when mistake_type  = 6 then 6  --上环节建包异常
           when mistake_type0 = 6 then 6
           when mistake_type2 = 6 then 6
    ---
           when mistake_type  = 7 then 7  --交叉带建包
           when mistake_type0 = 7 then 7
           when mistake_type2 = 7 then 7
    ---
           when mistake_type  = 10 then 10  --中心发件做删除扫描
           when mistake_type0 = 10 then 10
           when mistake_type2 = 10 then 10

    ---
           when mistake_type  = 11 then 11  --人为因素
           when mistake_type0 = 11 then 11
           when mistake_type2 = 11 then 11
           else mistake_type end
                                         as mistake_type
     ,is_delete
     ,virt_code
     ,virt_name
     ,provider_id
     ,provider_desc
     ,arrived_next_code
     ,arrived_next_name
     ,create_package_org_code
     ,create_package_org_name
     ,create_bagg_opt_org
     ,main_route_code           ---add jixk 20230907
     ,main_route_name           ---add jixk 20230907
     ,standby_route_code        ---add jixk 20230907
     ,standby_route_name        ---add jixk 20230907
     ,last_route_code           ---add jixk 20230907
     ,last_route_name           ---add jixk 20230907
     ,dispatch_code             ---add jixk 20230907
     ,temp_shipment_no
     ,first_end_org_code
     ,first_end_org_name
     ,mistake_type0  --错发类型
     ,mistake_type  --错发类型
     ,mistake_type2  --错发类型
     ,main_route_code0
     ,main_route_name0
     ,standby_route_code0
     ,standby_route_name0
     ,last_route_code0
     ,last_route_name0
     ,main_route_code2
     ,main_route_name2
     ,standby_route_code2
     ,standby_route_name2
     ,last_route_code2
     ,last_route_name2
     ,'{{ execution_date | cst_ds }}'    as dt
from
    (
        select date(res.sign_time)                as date_time --查询日期
   ,res.waybill_no                    as bill_code
   ,res.ordersource_name
   ,res.package_code
   ,res.package_number                  as  package_number  --集包编码
   ,res.scan_user                                --扫描人
   ,res.scan_user_code                           --扫描员工号
   ,res.pistol_id            as scan_pistol_id   --扫描设备编码
   ,res.tab_bagging_source   as scan_data_source
   ,res.send_time
   ,res.arr_next_name_desc
   ,res.arr_next_code_desc
   ,net4.name                as next_station
   ,res.next_station_code
   ,net2.agent_code
   ,net2.agent_name
   ,res.center_name       --中心名称
   ,res.center_code       --中心编码
   ,net3.name          as dest_center_name      --目的中心名称
   ,res.dest_center_code  --目的中心code
   ,res.sign_time          --签收时间
   ,res.sign_site          --签收网点
   ,res.sign_site_code     --签收网点编码
   ,res.out_distribution_code as sign_cande_codes    --签收网点可派货集散
   ,res.out_distribution_name as sign_cande_names    --签收网点可派货集散
   ,res.first_code    --一段码
   ,res.second_code   --二段码
   ,res.third_code    --三段码
   ,res.fst_dest_center_name
   ,res.fst_dest_center_code
   ,res.apply_type_code --退回件类型 --4退回件 --5转寄

---无一段码，无规划路由，调度指令，临时转运，一段码异常
   ,case
    when res.first_code is null or length(res.first_code)<>3 then 1          ---无一段码
        when res.first_code is not null AND fst_dest_center_code is null then 13 ---一段码无目的中心
        when res.route_code_desc is null and res.dispatch_task_no is null and res.temp_actual_shipment_no is null  then 2   ---无规划路由
        --     when array_contains(split(res.route_code_desc,',' ),next_station_code) =1 and coalesce(res.next_station_code,'') <>  coalesce(res.arrival_site_code,'')  then 12   --错装
        when array_contains(split(res.route_code_desc,',' ),next_station_code) =1  then 0    --无错发
        when res.dispatch_task_no is not null then 3  --调度指令
        when res.temp_actual_shipment_no is not null then 4  --临时转运
        when res.apply_type_code =4 then 8 --退回件
        when res.apply_type_code =5 then 9 --转寄件
        when array_contains(split(res.out_distribution_code,',' ),res.dest_center_code) <> 1 then 5 ---一段码异常
        when res.package_number is not null
             and res.is_tab_bagging_flag=0
             and res.create_package_org_code<>res.center_code
             and res.package_number <> first_code       then 6  --上环节建包异常
        when res.package_number is not null and res.is_tab_bagging_flag=1 and res.package_number <> first_code
             and res.create_package_org_code=res.center_code
             and res.tab_bagging_source='自动分拣设备'     then 7       --设备因素 改成交叉带建包
        when res.is_delete_flag=1 then 10 --中心发件做删除扫描
        else 11 --人为因素
end mistake_type  --错发类型
,res.is_delete_flag                        as is_delete
    ,coalesce(net2.virt_code,net2.agent_code)  as virt_code
    ,coalesce(net2.virt_name,net2.agent_name)  as virt_name
    ,net2.provider_id
    ,net2.provider_desc
    ,arrival_site_code                 as arrived_next_code
    ,arrival_site_name                 as arrived_next_name
    ,create_package_org_code
    ,create_package_org_name
    ,create_package_org_name           as create_bagg_opt_org
    ,res.main_route_code               as main_route_code
    ,res.main_route_name               as main_route_name
    ,res.standby_route_code            as standby_route_code
    ,res.standby_route_name            as standby_route_name
    ,''                                as last_route_code
    ,''                                as last_route_name
    ,res.dispatch_task_no              as dispatch_code
    ,res.temp_actual_shipment_no       as temp_shipment_no
    ,res.first_end_org_code            as first_end_org_code
    ,res.first_end_org_name            as first_end_org_name
    ---无一段码，无规划路由，调度指令，临时转运，一段码异常
    ,case
        when res.first_code is null or length(res.first_code)<>3 then 1          ---无一段码
        when res.first_code is not null AND fst_dest_center_code is null then 13 ---一段码无目的中心
        when res.route_code_desc0 is null and res.dispatch_task_no is null and res.temp_actual_shipment_no is null  then 2   ---无规划路由
        --     when array_contains(split(res.route_code_desc0,',' ),next_station_code) =1 and coalesce(res.next_station_code,'') <> coalesce(res.arrival_site_code,'')  then 12   --错装
        when array_contains(split(res.route_code_desc0,',' ),next_station_code) =1  then 0    --无错发
        when res.dispatch_task_no is not null then 3  --调度指令
        when res.temp_actual_shipment_no is not null then 4  --临时转运
        when res.apply_type_code =4 then 8 --退回件
        when res.apply_type_code =5 then 9 --转寄件
        when array_contains(split(res.out_distribution_code,',' ),res.dest_center_code) <> 1 then 5 ---一段码异常
        when res.package_number is not null and res.is_tab_bagging_flag=0
         and res.create_package_org_code<>res.center_code
         and res.package_number <> first_code       then 6  --上环节建包异常
        when res.package_number is not null and res.is_tab_bagging_flag=1 and res.package_number <> first_code
        and res.create_package_org_code=res.center_code
        and res.tab_bagging_source='自动分拣设备'     then 7       --设备因素 改成交叉带建包
        when res.is_delete_flag=1 then 10 --中心发件做删除扫描
        else 11 --人为因素
end mistake_type0  --错发类型
,case
        when res.first_code is null or length(res.first_code)<>3 then 1          ---无一段码
        when res.first_code is not null AND fst_dest_center_code is null then 13 ---一段码无目的中心
        when res.route_code_desc2 is null and res.dispatch_task_no is null and res.temp_actual_shipment_no is null  then 2   ---无规划路由
        --     when array_contains(split(res.route_code_desc2,',' ),next_station_code) =1 and coalesce(res.next_station_code,'') <> coalesce(res.arrival_site_code,'')  then 12   --错装
        when array_contains(split(res.route_code_desc2,',' ),next_station_code) =1  then 0    --无错发
        when res.dispatch_task_no is not null then 3  --调度指令
        when res.temp_actual_shipment_no is not null then 4  --临时转运
        when res.apply_type_code =4 then 8 --退回件
        when res.apply_type_code =5 then 9 --转寄件
        when array_contains(split(res.out_distribution_code,',' ),res.dest_center_code) <> 1 then 5 ---一段码异常
        when res.package_number is not null and res.is_tab_bagging_flag=0
         and res.create_package_org_code<>res.center_code
         and res.package_number <> first_code       then 6  --上环节建包异常
        when res.package_number is not null and res.is_tab_bagging_flag=1 and res.package_number <> first_code
        and res.create_package_org_code=res.center_code
        and res.tab_bagging_source='自动分拣设备'     then 7       --设备因素 改成交叉带建包
        when res.is_delete_flag=1 then 10 --中心发件做删除扫描
        else 11 --人为因素
end mistake_type2  --错发类型
,res.main_route_code0               as main_route_code0
    ,res.main_route_name0               as main_route_name0
    ,res.standby_route_code0            as standby_route_code0
    ,res.standby_route_name0            as standby_route_name0
    ,''                                 as last_route_code0
    ,''                                 as last_route_name0
    ,res.main_route_code2               as main_route_code2
    ,res.main_route_name2               as main_route_name2
    ,res.standby_route_code2            as standby_route_code2
    ,res.standby_route_name2            as standby_route_name2
    ,''                                 as last_route_code2
    ,''                                 as last_route_name2 
from transfer_mistake_merge_test1  res
left join jms_dim.dim_network_whole_massage net1 on net1.code=res.sign_site_code
left join jms_dim.dim_network_whole_massage net2 on net2.code=res.center_code
left join jms_dim.dim_network_whole_massage net3 on net3.code=res.dest_center_code and  net3.network_type = 4
left join jms_dim.dim_network_whole_massage net4 on net4.code=res.next_station_code
) t
distribute by dt,abs(hash(bill_code))%100
;